﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Domain;

namespace DataAcess
{
    public class ReservacionData
    {
         public String literalConexion;

        public ReservacionData(String literalConexion)
        {
            this.literalConexion = literalConexion;
        
        }

        public Reservacion registrarReservacion(Reservacion reservacion)
        {
            SqlConnection conexion = new SqlConnection(literalConexion);
            SqlCommand cmdInsertar = new SqlCommand();
            cmdInsertar.CommandText = "registrar_reservacion";
            cmdInsertar.Connection = conexion;
            cmdInsertar.CommandType = CommandType.StoredProcedure;
            SqlParameterCollection parametros = cmdInsertar.Parameters;
            SqlParameter parIdReservacion = new SqlParameter();
            parIdReservacion.Direction = ParameterDirection.Output;
            parIdReservacion.DbType = DbType.Int32;
            parIdReservacion.ParameterName = "@id_reservacion";

            parametros.Add(parIdReservacion);

            parametros.Add(new SqlParameter("@nombre_cliente", reservacion.NombreCliente));
            parametros.Add(new SqlParameter("@apellido_cliente", reservacion.ApellidosCliente));
            parametros.Add(new SqlParameter("@email_cliente", reservacion.Email));
            parametros.Add(new SqlParameter("@tarjeta_de_credito", reservacion.TarjetaCredito));
            parametros.Add(new SqlParameter("@id_habitacion", reservacion.IdHabitacion));
            conexion.Open();
            SqlTransaction transaccion = conexion.BeginTransaction();
            cmdInsertar.Transaction = transaccion;
            try
            {
               // cmdInsertar.Connection.Open();
                cmdInsertar.ExecuteNonQuery();
                reservacion.IdReservacion = Int32.Parse(cmdInsertar.Parameters["@id_reservacion"].Value.ToString());

                String sqlInsertaDetalleReservacion = "INSERT DetalleReservacion(id_reservacion,fecha_llegada,fecha_salida,total_pago)" + " values(@idReservacion,@fechaLlegada,@fechaSalida,@totalPago)";
                SqlCommand cmdInsertarDetalleResevacion = new SqlCommand(sqlInsertaDetalleReservacion, conexion);
                cmdInsertarDetalleResevacion.Transaction = transaccion;
                SqlParameterCollection parametrosDetalleReservacion = cmdInsertarDetalleResevacion.Parameters;
                
                parametrosDetalleReservacion.Add(new SqlParameter("@idReservacion", reservacion.IdReservacion));
                parametrosDetalleReservacion.Add(new SqlParameter("@fechaLlegada", reservacion.DetalleReservacion.FechaLlegada));
                parametrosDetalleReservacion.Add(new SqlParameter("@fechaSalida", reservacion.DetalleReservacion.FechaSalida));
                parametrosDetalleReservacion.Add(new SqlParameter("@totalPago", reservacion.DetalleReservacion.TotalPago));
                
              
                cmdInsertarDetalleResevacion.ExecuteNonQuery();
                transaccion.Commit();
            }
            catch (Exception)
            {
                if (conexion != null)
                {
                    transaccion.Rollback();
                }
            }
            finally
            {
                if (conexion != null)
                {
                    conexion.Close();
                }
            }
            cmdInsertar.Connection.Close();
            return reservacion;

        }
        public LinkedList<DetalleReservacion> DetalleReservacionesXFecha(DateTime fechaIngreso)
        {
            SqlConnection conexion = new SqlConnection();
            conexion.ConnectionString = literalConexion;
            SqlCommand cmdConsulta = new SqlCommand();
            cmdConsulta.CommandText = "retorna_detalle_res";
            cmdConsulta.Connection = conexion;
            cmdConsulta.CommandType = CommandType.StoredProcedure;
            SqlParameterCollection parametros = cmdConsulta.Parameters;
            parametros.Add(new SqlParameter("@fecha_entrada", fechaIngreso));
            conexion.Open();
            SqlDataReader reader = cmdConsulta.ExecuteReader();
            LinkedList<DetalleReservacion> detalles = new LinkedList<DetalleReservacion>();
            while (reader.Read())
            {
                DetalleReservacion detalleRes = new DetalleReservacion();
                detalleRes.IdDetelleReservacion = reader.GetInt32(0);
                detalleRes.FechaLlegada = reader.GetDateTime(1);
                detalleRes.FechaSalida = reader.GetDateTime(2);
                detalleRes.TotalPago = (float)reader.GetDouble(3);

                detalles.AddLast(detalleRes);

            }
            conexion.Close();

            return detalles;
        }

        public LinkedList<Reservacion> RecuperaReservaciones()
        {
            String sqlSelect = "Select * from Reservacion";
            SqlConnection conexion = new SqlConnection();
            conexion.ConnectionString = literalConexion;
            SqlCommand cmdRecuperaReserva = new SqlCommand(sqlSelect, conexion);
            conexion.Open();
            SqlDataReader reader = cmdRecuperaReserva.ExecuteReader();
            LinkedList<Reservacion> reservaciones = new LinkedList<Reservacion>();
            while (reader.Read())
            {
                Reservacion reservacion = new Reservacion();
                reservacion.IdReservacion = reader.GetInt32(0);
                reservacion.NombreCliente = reader.GetString(1);
                reservacion.ApellidosCliente = reader.GetString(2);
                reservacion.Email = reader.GetString(3);
                reservacion.TarjetaCredito = reader.GetString(4);
                reservacion.IdHabitacion = reader.GetInt32(5);

                reservaciones.AddLast(reservacion);
            }
            conexion.Close();
            return reservaciones;
        }

        public Reservacion RecuperaReservacionXID(int idReservacion)
        {
            String sqlSelect = "Select * from Reservacion where id_reservacion=" + idReservacion;
            SqlConnection conexion = new SqlConnection();
            conexion.ConnectionString = literalConexion;
            SqlCommand cmdRecuperaReserva = new SqlCommand(sqlSelect, conexion);
            conexion.Open();
            SqlDataReader reader = cmdRecuperaReserva.ExecuteReader();
            Reservacion reservacion = new Reservacion();
            if (reader.Read())
            {

                reservacion.IdReservacion = reader.GetInt32(0);
                reservacion.NombreCliente = reader.GetString(1);
                reservacion.ApellidosCliente = reader.GetString(2);
                reservacion.Email = reader.GetString(3);
                reservacion.TarjetaCredito = reader.GetString(4);
                reservacion.IdHabitacion = reader.GetInt32(5);
            }
            conexion.Close();
            return reservacion;
        }

        public DetalleReservacion RecuperaDetalleReservacionXID(int idReservacion)
        {
            String sqlSelect = "Select * from DetalleReservacion where id_reservacion=" + idReservacion;
            SqlConnection conexion = new SqlConnection();
            conexion.ConnectionString = literalConexion;
            SqlCommand cmdRecuperaReserva = new SqlCommand(sqlSelect, conexion);
            conexion.Open();
            SqlDataReader reader = cmdRecuperaReserva.ExecuteReader();
            DetalleReservacion detalle = new DetalleReservacion();
            if (reader.Read())
            {
                detalle.IdDetelleReservacion = reader.GetInt32(0);
                detalle.FechaLlegada = reader.GetDateTime(1);
                detalle.FechaSalida = reader.GetDateTime(2);
                detalle.TotalPago = (float)reader.GetDouble(3);
            }
            conexion.Close();
            return detalle;
        }

        public void EliminaReservacion(int idReservacion)
        {
            String sqlSelect = "Delete Reservacion where id_reservacion=" + idReservacion;
            SqlConnection conexion = new SqlConnection();
            conexion.ConnectionString = literalConexion;
            SqlCommand cmdEliminaReserva = new SqlCommand(sqlSelect, conexion);
            conexion.Open();
            try
            {
                cmdEliminaReserva.ExecuteNonQuery();
            }
            catch (Exception) { }
            conexion.Close();
        }

    }
}
